package com.zzzzzz.posts;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import com.zzzzzz.BaseDao;
import com.zzzzzz.common.page.Page;

@Repository
public class CommentDao extends BaseDao {
	
	private static final String ADD = 											"insert into comment(id, title, content, user_id, post_id) values (:title, :content, :userId, :postId)";
	private static final String EDIT = 											"update comment set title=:title, content=:content where id=:id";
	
	private static final String FIND_POSTSCOMMENTVIEW_BY_ID = 					"select p.id, p.title, p.content, p.pic_id, p.add_time, p.upd_time, pc.looks, pc.loves, c.id as comment_id, c.title as comment_title, c.content as comment_content, p,user_id, u.name as user_name, u.pic_id as user_pic_id from post p inner join post_count pc on p.id = pc.post_id inner join comment c on p.id = c.post_id inner join user u on c.user_id = u.id where p.id = ?";
	
	private static final String FIND_POSTSCOMMENTVIEW_BY_TAGID_ORDERBY_XX = 	"select p.id, p.title, p.content, p.pic_id, p.add_time, p.upd_time, pc.looks, pc.loves, c.id as comment_id, c.title as comment_title, c.content as comment_content, p,user_id, u.name as user_name, u.pic_id as user_pic_id from post p inner join post_count pc on p.id = pc.post_id inner join post_tag pt on p.id = pt.post_id inner join comment c on p.id = c.post_id inner join user u on c.user_id = u.id where pt.tag_id = ? and pt.status = 0 and p.status = 0 order by ";
	private static final String FIND_POSTSCOMMENTVIEW_COUNT_BY_TAGID = 			"select count(1) as count from post p inner join post_count pc on p.id = pc.post_id inner join post_tag pt on p.id = pt.post_id inner join comment c on p.id = c.post_id inner join user u on c.user_id = u.id where pt.tag_id = ? and pt.status = 0 and p.status = 0 order by ";
	
	protected class PostCommentViewRowMapper implements RowMapper<PostCommentView> {
		public PostCommentView mapRow(ResultSet rs, int rn) throws SQLException {
			PostCommentView postCommentView = new PostCommentView();
			postCommentView.setId(rs.getLong("id"));
			postCommentView.setTitle(rs.getString("title"));
			postCommentView.setContent(rs.getString("content"));
			postCommentView.setAddTime(rs.getTimestamp("add_time"));
			postCommentView.setUpdTime(rs.getTimestamp("upd_time"));
			postCommentView.setLooks(rs.getInt("looks"));
			postCommentView.setLoves(rs.getInt("loves"));
			postCommentView.setCommentId(rs.getLong("comment_id"));
			postCommentView.setCommentTitle(rs.getString("comment_title"));
			postCommentView.setCommentContent(rs.getString("comment_content"));
			postCommentView.setUserId(rs.getLong("user_id"));
			postCommentView.setUserName(rs.getString("user_name"));
			postCommentView.setUserPicId(rs.getString("user_pic_id"));
			return postCommentView;
		}
	}
	
	protected class PostViewRowMapper implements RowMapper<PostView> {
		public PostView mapRow(ResultSet rs, int rn) throws SQLException {
			PostView postView = new PostView();
			postView.setPostId(rs.getLong("id"));
			postView.setTitle(rs.getString("title"));
			postView.setContent(rs.getString("content"));
			postView.setPicId(rs.getString("pic_id"));
			postView.setUpdTime(rs.getTimestamp("upd_time"));
			postView.setLooks(rs.getInt("looks"));
			postView.setLoves(rs.getInt("loves"));
			postView.setUserId(rs.getLong("user_id"));
			postView.setUserName(rs.getString("user_name"));
			postView.setUserPicId(rs.getString("user_pic_id"));
			return postView;
		}
	}
	
	public void add(Comment comment) {
		Long id = updateForLongKey(ADD, comment);
		comment.setId(id);
	}

	public void edit(Comment comment) {
		update(EDIT, comment);
	}
	
	public PostCommentView findById(Long id){
		return findOne(FIND_POSTSCOMMENTVIEW_BY_ID, new PostCommentViewRowMapper(), id);
	}
	
	public Page<PostCommentView> findPostCommentViewByTagIdOrderBy(Integer tagId, String orderByField, Page<PostCommentView> page){
		MapSqlParameterSource paramMap = new MapSqlParameterSource();
		paramMap.addValue("tagId", tagId);
		
		StringBuilder sql = new StringBuilder(FIND_POSTSCOMMENTVIEW_BY_TAGID_ORDERBY_XX);
		sql.append(orderByField);
		sql.append(limit(paramMap, page));
		
		return findPage(page,sql.toString(), FIND_POSTSCOMMENTVIEW_COUNT_BY_TAGID, paramMap, new PostCommentViewRowMapper());
	}
	
}
